package com.bootdo.common.excel;

import com.bootdo.common.utils.DateUtils;
import com.bootdo.common.utils.StringUtils;
import com.google.common.collect.Lists;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * 导入积分商品excel工具类
 */
public class ProjectExcelUtil {

    public final static String Excel_2003 = ".xls"; //2003 版本的excel
    public final static String Excel_2007 = ".xlsx"; //2007 版本的excel

    /**
     * 导入商品excel
      * @param filePath
     * @param
     */
    public static List< List<String> > readProjectExcel(String filePath) throws IOException, InvalidFormatException {
        Workbook workbook = getWorkbook(filePath);
        List< List<String> > contend = Lists.newArrayList();
        if(workbook !=  null){
            //获取工作簿中电子表格的数量
            int numberOfSheets = workbook.getNumberOfSheets();
            // 遍历所有表
            for (int i = 0; i < numberOfSheets; i++) {
                 Sheet sheet = workbook.getSheetAt(i);
                // 非空验证
                if (null == sheet) {
                    continue;
                }
                // 单元格列表
                contend  = readExcelToList(sheet);
            }
        }
        return contend;
    }


    /**
     * 遍历行列
     * @param sheet
     * @return
     */
    private static   List< List<String> > readExcelToList(Sheet sheet){
        List< List<String> >  l = new ArrayList< List<String>>();

      if(sheet != null){
          // 遍历当前表中所有行
          for (int i = sheet.getFirstRowNum()+1; i <=sheet.getLastRowNum() ; i++) {
              // 获取当前行
              Row row = sheet.getRow(i);
              // 非空验证
              if (null == row) {
                  continue;
              }
              StringBuilder sb = new StringBuilder();
              List<String> rol = new ArrayList<String>();
              // 遍历当前行中所有单元格
              for (int j = row.getFirstCellNum(); j <=row.getLastCellNum() ; j++) {
                  // 获取当前单元格
                  Cell cell = row.getCell(j);
          /*        // 非空验证
                  if (null == cell) {
                      continue;
                  }*/
                  rol.add(formatCellValue(cell).toString());
              }
              l.add(rol);
          }
      }
      return  l;
    }


    /**
     * 格式化单元格value
     * @param cell
     * @return
     */
    private static Object formatCellValue(Cell cell) {
        Object cellValue = null;

        if(cell == null){
            cellValue = "";
        }else{
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    cellValue = cell.getRichStringCellValue().toString();
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    // 获取具体类型
                    String dataFormat = cell.getCellStyle().getDataFormatString();
                    // 具体类型判断

                    if (("General").equalsIgnoreCase(dataFormat)) {
                        cellValue = new DecimalFormat("0.00").format(cell.getNumericCellValue());
                    } else if (("m/d/yy").equalsIgnoreCase(dataFormat)) {
                        cellValue = DateUtils.format(cell.getDateCellValue(), DateUtils.DATE_TIME_PATTERN);
                    } else {
                        cellValue = 0;
                    }

                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    cellValue = cell.getBooleanCellValue();
                    break;
                case Cell.CELL_TYPE_BLANK:
                    cellValue = "";
                    break;
                default:
                    break;
            }
        }


        return cellValue;
    }
    /**
     *
     * @param filePath
     * @return
     */
    private static Workbook getWorkbook(String filePath) throws IOException, InvalidFormatException {
        InputStream is = null;
        Workbook wb = null;
        if(StringUtils.isEmpty(filePath)){
            throw new IllegalArgumentException("文件路径不存在");
        }else{
            String suffix = getSuffiex(filePath);
            if(StringUtils.isEmpty(suffix)){
                throw  new IllegalArgumentException("文件后缀不能为空");
            }
            if(suffix.equals(Excel_2003) || suffix.equals(Excel_2007)){
                try {
                    is = new FileInputStream(filePath);
                    wb = WorkbookFactory.create(is);
                } finally {
                    if(is != null){
                        is.close();
                    }
                    if(wb != null){
                        wb.close();
                    }
                }
            }else{
                throw  new IllegalArgumentException("文件不是Excel文件");
            }
        }
        return wb;
    }


    /**
     * 获得文件的后缀
     * @param filePath
     * @return
     */
    public  static String getSuffiex(String filePath){
         if(StringUtils.isBlank(filePath)){
             return "";
         }
         int index = filePath.lastIndexOf(".");
         if(index == -1){
             return "";
         }
         return filePath.substring(index, filePath.length());
    }
}
